package android.theHead;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class TeamDBAdapter{

	private static final String TEAM_DATABASE_NAME = "teamData.db";
	private static final int DB_VERSION = 1;

	private static final String INFO_TABLE = "teamTable";
	private static final String CLUB_TABLE = "clubTable";
	private static final String READINGS_TABLE = "readingsTable";
	private static final String VERSION_TABLE = "versionTable";
	
	// key is only defined once because it is used in every table
	public static final String KEY = "key";
	public static final int KEY_COLUMN = 0;
	
	// info table		
	public static final String KEY_INFO_TEAMID = "team_id";
	public static final int INFO_TEAMID_COLUMN = 1;
	
	public static final String KEY_INFO_TEAM_NAME = "team_name";
	public static final int INFO_TEAM_NAME_COLUMN = 2;
	
	public static final String KEY_INFO_CLUBID = "club_id";
	public static final int INFO_CLUBID_COLUMN = 3;
	
	public static final String KEY_INFO_BOATNAME = "boatname";
	public static final int INFO_BOATNAME_COLUMN = 4;
	
	public static final String KEY_INFO_COACHES = "coaches";
	public static final int INFO_COACHES_COLUMN = 5;
	
	public static final String KEY_INFO_COX = "cox";
	public static final int INFO_COX_COLUMN = 6;
	
	public static final String KEY_INFO_ROWER_BOW = "bow";
	public static final int INFO_ROWER_BOW_COLUMN = 7;

	public static final String KEY_INFO_ROWER2 = "rower_2";
	public static final int INFO_ROWER2_COLUMN = 8;
	
	public static final String KEY_INFO_ROWER3 = "rower_3";
	public static final int INFO_ROWER3_COLUMN = 9;
	
	public static final String KEY_INFO_ROWER4 = "rower_4";
	public static final int INFO_ROWER4_COLUMN = 10;
	
	public static final String KEY_INFO_ROWER5 = "rower_5";
	public static final int INFO_ROWER5_COLUMN = 11;
	
	public static final String KEY_INFO_ROWER6 = "rower_6";
	public static final int INFO_ROWER6_COLUMN = 12;
	
	public static final String KEY_INFO_ROWER7 = "rower_7";
	public static final int INFO_ROWER7_COLUMN = 13;
	
	public static final String KEY_INFO_ROWER_STROKE = "stroke";
	public static final int INFO_ROWER_STROKE_COLUMN = 14;
	
	public static final String KEY_INFO_DIVISION = "division";
	public static final int INFO_DIVISION_COLUMN = 15;
	
	
	// club table
	public static final String KEY_CLUB_ID = "club_id";
	public static final int CLUB_ID_COLUMN = 1;
	
	public static final String KEY_CLUB_NAME = "name";
	public static final int CLUB_NAME_COLUMN = 2;
	
	public static final String KEY_CLUB_CITY = "city";
	public static final int CLUB_CITY_COLUMN = 3;
	
	public static final String KEY_CLUB_COUNTRY = "country";
	public static final int CLUB_COUNTRY_COLUMN = 4;
	
	// readings table
	public static final String KEY_READINGS_TEAMID = "team_id";
	public static final int READINGS_WEIGHT_COLUMN = 1;
	
	public static final String KEY_READINGS_TIMESTAMP = "timestamp";
	public static final int READINGS_TIMESTAMP_COLUMN = 2;
	
	public static final String KEY_READINGS_SPEED = "speed";
	public static final int READINGS_SPEED_COLUMN = 3;
	
	public static final String KEY_READINGS_AVGSPEED = "avg_speed";
	public static final int READINGS_AVGSPEED_COLUMN = 4;
	
	public static final String KEY_READINGS_TEMPO = "tempo";
	public static final int READINGS_TEMPO_COLUMN = 5;
	
	public static final String KEY_READINGS_AVGTEMPO = "avg_tempo";
	public static final int READINGS_AVGTEMPO_COLUMN = 6;
	
	public static final String KEY_READINGS_LONGITUDE = "longitude";
	public static final int READINGS_LONGITUDE_COLUMN = 7;
	
	public static final String KEY_READINGS_LATITUDE = "latitude";
	public static final int READINGS_LATITUDE_COLUMN = 8;
	
	public static final String KEY_READINGS_BEARING = "bearing";
	public static final int READINGS_BEARING_COLUMN = 9;
	
	public static final String KEY_READINGS_DISTANCEFROMSTART = "distance_from_start";
	public static final int READINGS_DISTANCEFROMSTART_COLUMN = 10;
	
	public static final String KEY_READINGS_REALDISTANCE = "real_distance";
	public static final int READINGS_REALDISTANCE_COLUMN = 11;
	
	public static final String KEY_READINGS_DIVISIONRANKNG = "division_ranking";
	public static final int READINGS_DIVISIONRAKNING_COLUMN = 12;
	
	
	
	public static final String KEY_VERSION_VERSION = "data_version";
	public static final int VERSION_VERSION_COLUMN = 1;

	private static final String INFO_TABLE_CREATE = "create table " +
		INFO_TABLE + " (" + KEY + " integer primary key autoincrement, " +
		KEY_INFO_TEAMID + " INTEGER, " +
		KEY_INFO_TEAM_NAME + " TEXT, " +
		KEY_INFO_CLUBID + " TEXT, " +
		KEY_INFO_BOATNAME + " TEXT, " +
		KEY_INFO_COACHES + " TEXT, " +
		KEY_INFO_COX + " TEXT, " +
		KEY_INFO_ROWER_BOW + " TEXT, " +
		KEY_INFO_ROWER2+ " TEXT, " +
		KEY_INFO_ROWER3 + " TEXT, " +
		KEY_INFO_ROWER4 + " TEXT, " +
		KEY_INFO_ROWER5 + " TEXT, " +
		KEY_INFO_ROWER6 + " TEXT, " +
		KEY_INFO_ROWER7 + " TEXT, " +
		KEY_INFO_ROWER_STROKE + " TEXT, " +
		KEY_INFO_DIVISION + " TEXT" +
	");";
	
	private static final String CLUB_TABLE_CREATE = "create table " +
		CLUB_TABLE + " (" + KEY + " integer primary key autoincrement, " +
		KEY_CLUB_ID + " TEXT, " +
		KEY_CLUB_NAME + " TEXT, " +
		KEY_CLUB_CITY + " TEXT, " +
		KEY_CLUB_COUNTRY + " TEXT" +
	");";
	
	private static final String READINGS_TABLE_CREATE = "create table " +
		READINGS_TABLE + " (" + KEY + " integer primary key autoincrement, " +
		KEY_READINGS_TEAMID + " INTEGER, " +
		KEY_READINGS_TIMESTAMP + " TEXT, " +
		KEY_READINGS_SPEED + " REAL, " +
		KEY_READINGS_AVGSPEED + " REAL, " +
		KEY_READINGS_TEMPO + " REAL, " +
		KEY_READINGS_AVGTEMPO + " REAL, " +
		KEY_READINGS_LONGITUDE + " REAL, " +
		KEY_READINGS_LATITUDE + " REAL, " +
		KEY_READINGS_BEARING + " REAL, " +
		KEY_READINGS_DISTANCEFROMSTART + " REAL, " +
		KEY_READINGS_REALDISTANCE + " REAL, " +
		KEY_READINGS_DIVISIONRANKNG + " INTEGER" +
	");";
	
	private static final String VERSION_TABLE_CREATE = "create table " +
		VERSION_TABLE + " (" + KEY + " integer primary key autoincrement, " +
		KEY_VERSION_VERSION + " INTEGER);";

	// Variable to hold the database instance
	private SQLiteDatabase db;
	// Context of the application using the database.
	private final Context context;
	// Database open/upgrade helper
	private HeadDbHelper dbHelper;
	
	public TeamDBAdapter(Context context){
		this.context = context;
		this.dbHelper = new HeadDbHelper(context, TEAM_DATABASE_NAME, null, DB_VERSION);
	}
	
	public void updateTeamInfo(){
		int dataVersion = -1;
		Cursor c = db.rawQuery("select * from " + VERSION_TABLE, null);
		if (c.getCount()!=0){
			c.moveToLast();
			dataVersion = c.getInt(VERSION_VERSION_COLUMN);
		}
		c.close();
		Log.i("TeamDBAdater","DataVersion: " + dataVersion);
		GetData dataGetter = new GetData();
		String result = dataGetter.connect("http://www.head.pmast.de/getTeamInfo.php?version=" + dataVersion);
		Log.i("TeamDBAdapter", "get Result: " + result);
		Log.i("TeamDBAdapter", "Result lengt: " + result.length());
		
        JSONObject jsonResults = null;
        try {
        	jsonResults = new JSONObject(result);
            if (jsonResults.has("version")){
            	this.db.execSQL("insert into " + VERSION_TABLE + "("+KEY_VERSION_VERSION+") values (" + jsonResults.getString("version") + ")");
            	insertNewTeamInfo(jsonResults);
            	Log.i("TeamDBAdapter","get data: succes");
            } else {
            	Log.i("TeamDBAdapter","no data returned, data is up to date!");
            }
        } catch (JSONException e) {
    		e.printStackTrace();
    	}
	}
	
	public TeamDBAdapter open() throws SQLException {
		try {
			this.db = dbHelper.getWritableDatabase();
			return this;
		} catch (SQLiteException ex) {
			return null;
		}
	}
	
	public void close() {
		this.db.close();
	}
	
	// remove all but the latest readings per team from the readings database 
	public void cleanReadings(){
		this.db.execSQL("delete from " + READINGS_TABLE + " where " + KEY + " not in (select max(" + KEY + ") from " + READINGS_TABLE + " group by " + KEY_READINGS_TEAMID + ")");
	}
	
	public Cursor getTeamInfo(int teamId){
		Cursor team = this.db.rawQuery("select teamTable.team_id as _id, teamTable.team_id as teamID, teamTable.team_name as teamName, teamTable.club_id as clubID, teamTable.boatname as boatName, teamTable.coaches as coaches, teamTable.cox as cox, teamTable.bow as bow, teamTable.rower_2 as rower_2, teamTable.rower_3 as rower_3, teamTable.rower_4 as rower_4, teamTable.rower_5 as rower_5, teamTable.rower_6 as rower_6, teamTable.rower_7 as rower_7, teamTable.stroke as stroke, teamTable.division as division, clubTable.name as clubName, clubTable.city as clubCity, clubTable.country as clubCountry from teamTable, clubTable where teamTable.club_id = clubTable.club_id and teamTable.team_id = " + teamId, null);
		if (team.getCount()==0){
			return null;
		} else {
			return team;
		}
	}
	
	
	public Cursor getTeamData(int teamId){
		Cursor team = this.db.rawQuery("select teamTable.team_id as _id, teamTable.team_id as teamID, teamTable.team_name as teamName, teamTable.club_id as clubID, teamTable.boatname as boatName, teamTable.coaches as coaches, teamTable.cox as cox, teamTable.bow as bow, teamTable.rower_2 as rower_2, teamTable.rower_3 as rower_3, teamTable.rower_4 as rower_4, teamTable.rower_5 as rower_5, teamTable.rower_6 as rower_6, teamTable.rower_7 as rower_7, teamTable.stroke as stroke, teamTable.division as division, clubTable.name as clubName, clubTable.city as clubCity, clubTable.country as clubCountry, readingsTable.timestamp as deviceUTCTime, readingsTable.speed as speed, readingsTable.avg_speed as avgSpeed, readingsTable.tempo as tempo, readingsTable.avg_tempo as avgTempo, readingsTable.longitude as longitude, readingsTable.latitude as latitude, readingsTable.bearing as bearing, readingsTable.distance_from_start as distanceFromStart, readingsTable.real_distance as realDistance, readingsTable.division_ranking as divisionRanking from teamTable, clubTable, readingsTable where teamTable.club_id = clubTable.club_id and teamTable.team_id = readingsTable.team_id and teamTable.team_id = " + teamId + " order by readingsTable.timestamp ASC", null);
		if (team.getCount()==0){
			return null;
		} else {
			return team;
		}
	}
	
	
	public Cursor getAllInfo(){
		Cursor team = this.db.rawQuery("select teamTable.team_id as _id, teamTable.team_id as teamID, teamTable.team_name as teamName, teamTable.club_id as clubID, teamTable.boatname as boatName, teamTable.coaches as coaches, teamTable.cox as cox, teamTable.bow as bow, teamTable.rower_2 as rower_2, teamTable.rower_3 as rower_3, teamTable.rower_4 as rower_4, teamTable.rower_5 as rower_5, teamTable.rower_6 as rower_6, teamTable.rower_7 as rower_7, teamTable.stroke as stroke, teamTable.division as division, clubTable.name as clubName, clubTable.city as clubCity, clubTable.country as clubCountry, readingsTable.timestamp as deviceUTCTime, readingsTable.speed as speed, readingsTable.avg_speed as avgSpeed, readingsTable.tempo as tempo, readingsTable.avg_tempo as avgTempo, readingsTable.longitude as longitude, readingsTable.latitude as latitude, readingsTable.bearing as bearing, readingsTable.distance_from_start as distanceFromStart, readingsTable.real_distance as realDistance, readingsTable.division_ranking as divisionRanking from teamTable, clubTable, readingsTable where teamTable.club_id = clubTable.club_id and teamTable.team_id = readingsTable.team_id", null);
		if (team.getCount()==0){
			return null;
		} else {
			return team;
		}
	}
	
	public void insertNewTeamInfo(JSONObject data){
		if (data.has("teams")){
			try {
				JSONArray teams = data.getJSONArray("teams");
				for (int i = 0; i < teams.length(); ++i) {
				    JSONObject team = teams.getJSONObject(i);
				    
				    ContentValues newValues = new ContentValues();
					// Assign values for each row.
					newValues.put(KEY_INFO_TEAMID, team.getString("teamID"));
					newValues.put(KEY_INFO_TEAM_NAME, team.getString("teamName"));
					newValues.put(KEY_INFO_CLUBID, team.getString("clubID"));
					newValues.put(KEY_INFO_BOATNAME, team.getString("boat"));
					newValues.put(KEY_INFO_COACHES, team.getString("coaches"));
					newValues.put(KEY_INFO_COX, team.getString("cox"));
					newValues.put(KEY_INFO_ROWER_BOW, team.getString("bow"));
					newValues.put(KEY_INFO_ROWER2, team.getString("rower2"));
					newValues.put(KEY_INFO_ROWER3, team.getString("rower3"));
					newValues.put(KEY_INFO_ROWER4, team.getString("rower4"));
					newValues.put(KEY_INFO_ROWER5, team.getString("rower5"));
					newValues.put(KEY_INFO_ROWER6, team.getString("rower6"));
					newValues.put(KEY_INFO_ROWER7, team.getString("rower7"));
					newValues.put(KEY_INFO_ROWER_STROKE, team.getString("stroke"));
					newValues.put(KEY_INFO_DIVISION, team.getString("division"));
					
					this.db.insert(INFO_TABLE, null, newValues);
				}
			} catch (JSONException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (data.has("clubs")){
			try {
				JSONArray clubs = data.getJSONArray("clubs");
				for (int i = 0; i < clubs.length(); ++i) {
				    JSONObject club = clubs.getJSONObject(i);
				    
				    ContentValues newValues = new ContentValues();
					// Assign values for each row.
					newValues.put(KEY_CLUB_ID, club.getString("clubID"));
					newValues.put(KEY_CLUB_NAME, club.getString("name"));
					newValues.put(KEY_CLUB_CITY, club.getString("city"));
					newValues.put(KEY_CLUB_COUNTRY, club.getString("country"));
					
					this.db.insert(CLUB_TABLE, null, newValues);
				}
			} catch (JSONException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public void insertNewReadings(JSONObject data){
		try {
			if (data.has("data")){
				JSONArray dataArray = data.getJSONArray("data");
				JSONObject dataItem;
				Log.i("TeamDBAdapter","length of received data: " + dataArray.length());
				for (int i=0; i<dataArray.length(); i++){
					dataItem = dataArray.getJSONObject(i);
					ContentValues newValues = new ContentValues();

					newValues.put(KEY_READINGS_TEAMID, dataItem.getString("teamID"));
					newValues.put(KEY_READINGS_TIMESTAMP, dataItem.getString("deviceUtcTime"));
					newValues.put(KEY_READINGS_SPEED, dataItem.getString("speed"));
//					newValues.put(KEY_READINGS_AVGSPEED, dataItem.getString("coaches"));
					newValues.put(KEY_READINGS_TEMPO, dataItem.getString("tempo"));
//					newValues.put(KEY_READINGS_AVGTEMPO, dataItem.getString("bow"));
					newValues.put(KEY_READINGS_LONGITUDE, dataItem.getString("longitude"));
					newValues.put(KEY_READINGS_LATITUDE, dataItem.getString("latitude"));
					newValues.put(KEY_READINGS_BEARING, dataItem.getString("bearing"));
//					newValues.put(KEY_READINGS_DISTANCEFROMSTART, dataItem.getString("rower5"));
//					newValues.put(KEY_READINGS_REALDISTANCE, dataItem.getString("rower6"));
//					newValues.put(KEY_READINGS_DIVISIONRANKNG, dataItem.getString("rower7"));
					
					this.db.insert(READINGS_TABLE, null, newValues);
				}
			}
		} catch (JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	private static class HeadDbHelper extends SQLiteOpenHelper {
		
		public HeadDbHelper(Context context, String name, CursorFactory factory, int version) {
			super(context, name, factory, version);
		}
		
		// Called when no database exists in disk and the helper class needs
		// to create a new one.
		@Override
		public void onCreate(SQLiteDatabase _db) {
			_db.execSQL(INFO_TABLE_CREATE);
			_db.execSQL(CLUB_TABLE_CREATE);
			_db.execSQL(READINGS_TABLE_CREATE);
			_db.execSQL(VERSION_TABLE_CREATE);
		}
		
		// Called when there is a database version mismatch meaning that the version
		// of the database on disk needs to be upgraded to the current version.
		@Override
		public void onUpgrade(SQLiteDatabase _db, int _oldVersion, int _newVersion) {
			// Log the version upgrade.
			Log.w("TaskDBAdapter", "Upgrading from version " + _oldVersion + " to " + _newVersion + ", which will destroy all old data");

			// Upgrade the existing database to conform to the new version. Multiple
			// previous versions can be handled by comparing _oldVersion and _newVersion
			// values.
		
			// The simplest case is to drop the old table and create a new one.
			_db.execSQL("DROP TABLE IF EXISTS " + INFO_TABLE);
			_db.execSQL("DROP TABLE IF EXISTS " + CLUB_TABLE);
			_db.execSQL("DROP TABLE IF EXISTS " + READINGS_TABLE);
			_db.execSQL("DROP TABLE IF EXISTS " + VERSION_TABLE);
		
			// Create a new one.
			onCreate(_db);
		}
	}
	
}
